from mailmerge import MailMerge
import time
from openpyxl import load_workbook as open_excel
from openpyxl import Workbook


def load_shipping_list():
    data = []
    wb_result = open_excel("shipping_list.xlsx", data_only=True)
    table = wb_result.active
    rows = table.max_row
    cols = table.max_column
    for row in range(rows):
        row_data = []
        for col in range(cols):
            row_data.append(table.cell(row + 1, col + 1).value)
        if row_data[3] !=None:
            data.append(row_data)
    data.pop(0)  # remove the first row since it is the title, not real values
    return data


def calculate_total_box_NO(shipping_data):
    total_NO = 0
    for row in shipping_data:
        if row[3] != None:
            total_NO += row[3]
    # print(total_NO)
    return total_NO

# Save data to excel
def save_to_Excel(data, file):
    # Save the result to file
    # Init the result file
    wb = Workbook()
    ws1 = wb.active
    ws1.title = 'result'
    for row in data:
        ws1.append(row)
    wb.save(file)

# Save data to word using mailmerge
def save_to_word(box_label_list):
    doc_path = "template.docx"
    doc = MailMerge(doc_path)
    doc.merge(Sequence_NO=box_label_list)
    doc.write("箱贴.docx")

if __name__ == '__main__':
    print('Box Label Generator V2.0 is running...')
    shipping_data = load_shipping_list()
    total_NO = calculate_total_box_NO(shipping_data)
    print('Loading shipping list and template files......done')
    print("     SKU Item Number:", len(shipping_data))
    print("     Total Box Number:", total_NO)

    box_label_list = []
    label_str_4_excel = []
    sequence = 0
    for sku_row in shipping_data:
        i = 1
        while i <= sku_row[3]:
            sequence += 1
            i += 1
            box_label = {"Sequence_NO": sequence, "Total_NO": total_NO,
                         "Amount": sku_row[2], "SKU": sku_row[1], "Address": sku_row[4],
                         "Label_Text_Quantity": sku_row[5]}
            box_label_list.append(box_label)
            label_row = []
            label_row.append(str(sequence) + "/" + str(total_NO) + "\n"
                                     + sku_row[5] + ":" + str(sku_row[2]) + "\n"
                                     + sku_row[1] + "\n"
                                     + "\n"
                                     + sku_row[4])
            label_str_4_excel.append(label_row)

    save_to_word(box_label_list)
    #save_to_Excel(label_str_4_excel,"箱贴.xlsx")

    print('Box label file generated Successfully! The result is saved into excel file.')
    input("Press any key to exist.")

